﻿create database fat1_c1203i_s3_g2
go
use fat1_c1203i_s3_g2
go
create table customer
(
	customerId int primary key identity ,
	username varchar(25) not null,
	passwords varchar(25) not null,
	name varchar(25),
	birthday datetime,
	gender varchar(25),
	email varchar(200) not null,
	status varchar(25) not null
)
--select * from customer where status = 'subcrible'
insert into customer values('thanh','123456','van thanh','04-26-1989','boy','vanthanh26041989@gmail.com','normal')
insert into customer values('linh','123456','van thanh','04-26-1989','girl','linhnt_c00721@fpt.aptech.ac.vn','subcrible')
insert into customer values('quang','123456','van thanh','04-26-1989','boy','quangtm_a05097@fpt.aptech.ac.vn','subcrible')

go
create table contactEmail
(
	customerId int foreign key references customer(customerId),
	email varchar(100) not null,
	birthday datetime not null,
	name varchar(25) not null
)
insert into contactEmail values(2,'nguoimua12@gmail.com',GETDATE(),'linh')
insert into contactEmail values(2,'nguoiban1234567@gmail.com',GETDATE(),'quang')
insert into contactEmail values(3,'nguoimua12@gmail.com',GETDATE(),'thang')
insert into contactEmail values(3,'nguoiban1234567@gmail.com',GETDATE(),'minh')
go
create table subcribleType
(
	subcribleTypeId int primary key identity,
	subcrileName varchar(25) not null,
	price int not null
)
go
create table subcrible
(
	subcribleId int primary key identity,
	customerId int foreign key references customer(customerId),
	datefrom datetime not null,
	dateto datetime not null,
	subcribleTypeId int foreign key references subcribleType(subcribleTypeId),
	
)
go
create table typeEcard
(
	typeEcardId int primary key identity,
	typeCardName varchar(25) not null
)
--select * from typeEcard

insert into typeEcard values('Birthday')
insert into typeEcard values('Wedding')
insert into typeEcard values('NewYear')
insert into typeEcard values('Halloween')
insert into typeEcard values('Festivals')
insert into typeEcard values('valentive')
insert into typeEcard values('HappyHolidays')
insert into typeEcard values('Marry chistmas')
insert into typeEcard values('NewCard')
go
create table Ecard
(
	ecardId int primary key identity,
	urlImage varchar(200) not null,
	urlFlash varchar(200) not null,
	ecardName varchar(50) not null,
	addDate datetime not null,
	typeEcardId int foreign key references typeEcard(typeEcardId),
	counts int not null
)


--select typeCardName , SUM(Ecard.counts) as sent from Ecard inner join typeEcard on typeEcard.typeEcardId = Ecard.typeEcardId Group by  typeEcard.typeCardName order by typeEcard.typeCardName
--select * from Ecard
-- thiep chuc mung sinh  nhat
--insert into Ecard values('http://i.123g.us/c/birth_happybirthday/th/119311_th.gif','http://i.123g.us/c/birth_happybirthday/card/119311.swf','Wish You Health And Happiness.','07-11-2013',4,4)
--insert into Ecard values('http://i.123g.us/c/birth_happybirthday/th/114550_th.gif','http://i.123g.us/c/birth_happybirthday/card/114550.swf','A Card Full Of Surprise.','07-11-2013',1,0)
--insert into Ecard values('http://i.123g.us/c/birth_happybirthday/th/124599_th.gif','http://i.123g.us/c/birth_happybirthday/card/124599.swf','Beautiful Flowers For Your.','07-11-2013',1,0)
--insert into Ecard values('http://i.123g.us/c/birth_happybirthday/th/117069_th.gif','http://i.123g.us/c/birth_happybirthday/card/117069.swf','An Interactive Birthday Wish.','07-11-2013',1,0)
--insert into Ecard values('http://i.123g.us/c/birth_happybirthday/th/103005_th.gif','http://i.123g.us/c/birth_happybirthday/card/103005.swf','An Interactive Birthday Wish.','07-11-2013',1,0)
--thiep chuc mung nam moi
--update Ecard set counts = counts + 1  where EcardId = 1

-- thiep chuc mung sinh  nhat
insert into Ecard values('http://i.123g.us/c/birth_happybirthday/th/119311_th.gif','http://i.123g.us/c/birth_happybirthday/card/119311.swf','Wish You Health.','07-11-2013',1,0)
insert into Ecard values('http://i.123g.us/c/birth_happybirthday/th/114550_th.gif','http://i.123g.us/c/birth_happybirthday/card/114550.swf','A Card Full Of Surprise.','07-11-2013',1,0)
insert into Ecard values('http://i.123g.us/c/birth_happybirthday/th/124599_th.gif','http://i.123g.us/c/birth_happybirthday/card/124599.swf','Beautiful Flowers.','07-11-2013',1,0)
insert into Ecard values('http://i.123g.us/c/birth_happybirthday/th/117069_th.gif','http://i.123g.us/c/birth_happybirthday/card/117069.swf','An Interactive Birthday.','07-11-2013',1,0)
insert into Ecard values('http://i.123g.us/c/birth_happybirthday/th/303331_th.jpg','http://i.123g.us/c/birth_happybirthday/card/303331.swf','Wishing You Blessings.','07-11-2013',1,0)
insert into Ecard values('http://i.123g.us/c/birth_happybirthday/th/102896_th.gif','http://i.123g.us/c/birth_happybirthday/card/102896.swf','A Special Birthday.','07-11-2013',1,0)
insert into Ecard values('http://i.123g.us/c/birth_happybirthday/th/120570_th.gif','http://i.123g.us/c/birth_happybirthday/card/120570.swf','Wish You Smiles.','07-11-2013',1,0)
insert into Ecard values('http://i.123g.us/c/birth_happybirthday/th/120819_th.gif','http://i.123g.us/c/birth_happybirthday/card/120819.swf','This Day Be A Magical.','07-11-2013',1,0)
insert into Ecard values('http://i.123g.us/c/birth_happybirthday/th/123206_th.gif','http://i.123g.us/c/birth_happybirthday/card/123206.swf','That Celebrates You.','07-11-2013',1,0)
insert into Ecard values('http://i.123g.us/c/birth_happybirthday/th/123057_th.gif','http://i.123g.us/c/birth_happybirthday/card/123057.swf','Time To Celebrat.','07-11-2013',1,0)
insert into Ecard values('http://i.123g.us/c/birth_happybirthday/th/123923_th.gif','http://i.123g.us/c/birth_happybirthday/card/123923.swf','Special Day To Celebrate.','07-11-2013',1,0)
insert into Ecard values('http://i.123g.us/c/birth_happybirthday/th/122577_th.gif','http://i.123g.us/c/birth_happybirthday/card/122577.swf','Special Birthday Carniva.','07-11-2013',1,0)

--thiep dam cuoi
insert into Ecard values('http://i.123g.us/c/wed_wed_congrats/th/303886_th.jpg','http://i.123g.us/c/wed_wed_congrats/card/303886.swf','God Bless Your Marriage.','07-11-2013',2,0)
insert into Ecard values('http://i.123g.us/c/wed_wed_congrats/th/100008_th.gif','http://i.123g.us/c/wed_wed_congrats/card/100008.swf','Wedding Bells Ringing.','07-11-2013',2,0)
insert into Ecard values('http://i.123g.us/c/wed_wed_congrats/th/122236_th.gif','http://i.123g.us/c/wed_wed_congrats/card/122236.swf','Wedding Wishes For A Couple.','07-11-2013',2,0)
insert into Ecard values('http://i.123g.us/c/wed_wishes/th/115411_th.gif','http://i.123g.us/c/wed_wishes/card/115411.swf','Wedding Wishes For Lovely.','07-11-2013',2,0)
insert into Ecard values('http://i.123g.us/c/invp_wed/th/111460_th.gif','http://i.123g.us/c/invp_wed/card/111460.swf','Wedding Invitation.','07-11-2013',2,0)
insert into Ecard values('http://i.123g.us/c/wed_arndworld/th/110515_th.gif','http://i.123g.us/c/wed_arndworld/card/110515.swf','Christian Wedding.','07-11-2013',2,0)
insert into Ecard values('http://i.123g.us/c/wed_wed_marryme/th/112464_th.gif','http://i.123g.us/c/wed_wed_marryme/card/112464.swf','Anxious Moments.','07-11-2013',2,0)
insert into Ecard values('http://i.123g.us/c/wed_wed_marryme/th/112327_th.gif','http://i.123g.us/c/wed_wed_marryme/card/112327.swf','Will You Be Mine?','07-11-2013',2,0)
insert into Ecard values('http://i.123g.us/c/wed_justmarried/th/100055_th.gif','http://i.123g.us/c/wed_wed_marryme/card/112327.swf','Happy Ever After!','07-11-2013',2,0)
insert into Ecard values('http://i.123g.us/c/wed_announce/th/113623_th.gif','http://i.123g.us/c/wed_announce/card/113623.swf','The Date Card','07-11-2013',2,0)
insert into Ecard values('http://i.123g.us/c/wed_announce/th/112563_th.gif','http://i.123g.us/c/wed_announce/card/112563.swf','Save The Date!.','07-11-2013',2,0)
insert into Ecard values('http://i.123g.us/c/wed_wed_marryme/th/100045_th.gif','http://i.123g.us/c/wed_wed_marryme/card/100045.swf','Make My Wish come true!','07-11-2013',2,0)


--thiep chuc mung nam moi

insert into Ecard values('http://i.123g.us/c/ejan_ny_family/th/107806_th.gif','http://i.123g.us/c/ejan_ny_family/card/107806.swf','Happy New Year!','07-11-2013',3,0)
insert into Ecard values('http://i.123g.us/c/ejan_ny_happy/th/107887_th.gif','http://i.123g.us/c/ejan_ny_happy/card/107887.swf','Sparkling New Year','07-11-2013',3,0)
insert into Ecard values('http://i.123g.us/c/ejan_ny_happy/th/113379_th.gif','http://i.123g.us/c/ejan_ny_happy/card/113379.swf','Big New Year Hug!','07-11-2013',3,0)
insert into Ecard values('http://i.123g.us/c/ejan_ny_happy/th/113846_th.gif','http://i.123g.us/c/ejan_ny_happy/card/113846.swf','Heartfelt New Year Wishes!','07-11-2013',3,0)
insert into Ecard values('http://i.123g.us/c/ejan_ny_happy/th/113859_th.gif','http://i.123g.us/c/ejan_ny_happy/card/113859.swf','Loads Of New Year Hugs.','07-11-2013',3,0)
insert into Ecard values('http://i.123g.us/c/ejan_ny_happy/th/113854_th.gif','http://i.123g.us/c/ejan_ny_happy/card/113854.swf','New Year Teddy Hugs','07-11-2013',3,0)
insert into Ecard values('http://i.123g.us/c/ejan_ny_thanku/th/107829_th.gif','http://i.123g.us/c/ejan_ny_thanku/card/107829.swf','New Year Thanks!','07-11-2013',3,0)
insert into Ecard values('http://i.123g.us/c/ejan_ny_happy/th/109527_th.gif','http://i.123g.us/c/ejan_ny_happy/card/109527.swf','New Year Romantic Wish','07-11-2013',3,0)
insert into Ecard values('http://i.123g.us/c/ejan_ny_happy/th/122024_th.gif','http://i.123g.us/c/ejan_ny_happy/card/122024.swf','New Year Joy And Merriment','07-11-2013',3,0)
insert into Ecard values('http://i.123g.us/c/ejan_ny_happy/th/113850_th.gif','http://i.123g.us/c/ejan_ny_happy/card/113850.swf','Interactive New Year Wish.','07-11-2013',3,0)
insert into Ecard values('http://i.123g.us/c/ejan_chinese_happy/th/114115_th.gif','http://i.123g.us/c/ejan_chinese_happy/card/114115.swf','New Year Snake','07-11-2013',3,0)
insert into Ecard values('http://i.123g.us/c/ejan_ny_fireworks/th/107827_th.gif','http://i.123g.us/c/ejan_ny_fireworks/card/107827.swf','Sparkling New Year Wishes','07-11-2013',3,0)



--thiep chuc mung Halloween
insert into Ecard values('http://i.123g.us/c/eoct_hallo_thank/th/125091_th.gif','http://i.123g.us/c/eoct_hallo_thank/card/125091.swf','Halloween Thank U Hugs!','07-11-2013',4,0)
insert into Ecard values('http://i.123g.us/c/eoct_hallo_wishes/th/121387_th.gif','http://i.123g.us/c/eoct_hallo_wishes/card/121387.swf','Happy Halloween To U!','07-11-2013',4,0)
insert into Ecard values('http://i.123g.us/c/eoct_hallo_hauntedhouse/th/113189_th.gif','http://i.123g.us/c/eoct_hallo_hauntedhouse/card/113189.swf','Halloween Haunted!','07-11-2013',4,0)
insert into Ecard values('http://i.123g.us/c/eoct_hallo_wishes/th/310441_th.gif','http://i.123g.us/c/eoct_hallo_wishes/card/105128.swf','Magical Halloween!','07-11-2013',4,0)
insert into Ecard values('http://i.123g.us/c/eoct_hallo_wishes/th/105128_th.gif','http://i.123g.us/c/birth_happybirthday/card/114550.swf','Halloween Wishes','07-11-2013',4,0)
insert into Ecard values('http://i.123g.us/c/eoct_hallo_wishes/th/104945_th.gif','http://i.123g.us/c/eoct_hallo_wishes/card/104945.swf','Happy Halloween To You','07-11-2013',4,0)
insert into Ecard values('http://i.123g.us/c/eoct_hallo_wishes/th/113133_th.gif','http://i.123g.us/c/eoct_hallo_wishes/card/113133.swf','Create A Halloween!','07-11-2013',4,0)
insert into Ecard values('http://i.123g.us/c/eoct_hallo_wishes/th/104997_th.gif','http://i.123g.us/c/eoct_hallo_wishes/card/104997.swf','Surprise On Halloween!','07-11-2013',4,0)
insert into Ecard values('http://i.123g.us/c/eoct_hallo_wishes/th/111973_th.gif','http://i.123g.us/c/eoct_hallo_wishes/card/111973.swf','Dark Halloween Night!','07-11-2013',4,0)
insert into Ecard values('http://i.123g.us/c/eoct_hallo_wishes/th/104999_th.gif','http://i.123g.us/c/eoct_hallo_wishes/card/104999.swf','For Your Halloween','07-11-2013',4,0)
insert into Ecard values('http://i.123g.us/c/eoct_hallo_pumpkins/th/105071_th.gif','http://i.123g.us/c/eoct_hallo_pumpkins/card/105071.swf','Have A Terrific Halloween!','07-11-2013',4,0)
insert into Ecard values('http://i.123g.us/c/eoct_hallo_pumpkins/th/105074_th.gif','http://i.123g.us/c/eoct_hallo_pumpkins/card/105074.swf','Halloween Pumpkin!','07-11-2013',4,0)

--Thiep Festilvals
insert into Ecard values('http://i.123g.us/c/ejan_makarsankranti/th/125706_th.gif','http://i.123g.us/c/ejan_makarsankranti/card/125706.swf','Festive Spirit!','07-11-2013',5,0)
insert into Ecard values('http://i.123g.us/c/ejan_lohri/th/122238_th.gif','http://i.123g.us/c/ejan_lohri/card/122238.swf','Lohri Fun And Frolic','07-11-2013',5,0)
insert into Ecard values('http://i.123g.us/c/ejan_lohri/th/122227_th.gif','http://i.123g.us/c/ejan_lohri/card/122227.swf','Joyous Lohri','07-11-2013',5,0)
insert into Ecard values('http://i.123g.us/c/ejan_makarsankranti/th/121242_th.gif','http://i.123g.us/c/ejan_makarsankranti/card/121242.swf','Prosperity','07-11-2013',5,0)
insert into Ecard values('http://i.123g.us/c/edec_hanukkah_happy/th/107502_th.gif','http://i.123g.us/c/edec_hanukkah_happy/card/107502.swf','Festival Of Lights','07-11-2013',5,0)
insert into Ecard values('http://i.123g.us/c/ejan_lohri/th/122240_th.gif','http://i.123g.us/c/ejan_lohri/card/122240.swf','Prosperous Harvest','07-11-2013',5,0)
insert into Ecard values('http://i.123g.us/c/ejan_makarsankranti/th/121241_th.gif','http://i.123g.us/c/ejan_makarsankranti/card/121241.swf','Filled With Sweet','07-11-2013',5,0)
insert into Ecard values('http://i.123g.us/c/esep_harvestmoonfest/th/115794_th.gif','http://i.123g.us/c/esep_harvestmoonfest/card/115794.swf','SMoon Festival Fun','07-11-2013',5,0)
insert into Ecard values('http://i.123g.us/c/esep_harvestmoonfest/th/103717_th.gif','http://i.123g.us/c/esep_harvestmoonfest/card/103717.swf','Wonderful Moon Festival','07-11-2013',5,0)
insert into Ecard values('http://i.123g.us/c/esep_harvestmoonfest/th/119861_th.gif','http://i.123g.us/c/esep_harvestmoonfest/card/119861.swf','With Happiness','07-11-2013',5,0)
insert into Ecard values('http://i.123g.us/c/esep_harvestmoonfest/th/119856_th.gif','http://i.123g.us/c/esep_harvestmoonfest/card/119856.swf','Yummy Surprises','07-11-2013',5,0)
insert into Ecard values('http://i.123g.us/c/ejul_friendfestmissouri/th/123466_th.gif','http://i.123g.us/c/ejul_friendfestmissouri/card/123466.swf','Friendship Festival','07-11-2013',5,0)


--Thiệp Valentine
insert into Ecard values('http://i.123g.us/c/efeb_valen_happy/th/118053_th.gif','http://i.123g.us/c/efeb_valen_happy/card/118053.swf','Valentine Day','07-11-2013',6,0)
insert into Ecard values('http://i.123g.us/c/efeb_valen_happy/th/113826_th.gif','http://i.123g.us/c/efeb_valen_happy/card/113826.swf','Valentine Wishes!','07-11-2013',6,0)
insert into Ecard values('http://i.123g.us/c/efeb_valen_family/th/117771_th.gif','http://i.123g.us/c/efeb_valen_family/card/117771.swf','Valentine Day Hugs!','07-11-2013',6,0)
insert into Ecard values('http://i.123g.us/c/efeb_valen_happy/th/113838_th.gif','http://i.123g.us/c/efeb_valen_happy/card/113838.swf','Valentine Teddy Hugs!','07-11-2013',6,0)
insert into Ecard values('http://i.123g.us/c/efeb_valen_happy/th/113947_th.gif','http://i.123g.us/c/efeb_valen_happy/card/113947.swf','Valentine Smiley','07-11-2013',6,0)
insert into Ecard values('http://i.123g.us/c/efeb_valen_happy/th/122434_th.gif','http://i.123g.us/c/efeb_valen_happy/card/122434.swf','A Special Valentine','07-11-2013',6,0)
insert into Ecard values('http://i.123g.us/c/efeb_valen_happy/th/109551_th.gif','http://i.123g.us/c/efeb_valen_happy/card/109551.swf','A Romantic Valentine','07-11-2013',6,0)
insert into Ecard values('http://i.123g.us/c/efeb_valen_happy/th/107968_th.gif','http://i.123g.us/c/efeb_valen_happy/card/107968.swf','A Valentine Day','07-11-2013',6,0)
insert into Ecard values('http://i.123g.us/c/efeb_valen_happy/th/113564_th.gif','http://i.123g.us/c/efeb_valen_happy/card/113564.swf','Precious Valentine!','07-11-2013',6,0)
insert into Ecard values('http://i.123g.us/c/efeb_valen_happy/th/107967_th.gif','http://i.123g.us/c/efeb_valen_happy/card/107967.swf','Rose On Valentine Day!','07-11-2013',6,0)
insert into Ecard values('http://i.123g.us/c/efeb_valen_happy/th/107401_th.gif','http://i.123g.us/c/efeb_valen_happy/card/107401.swf','Valentine Thoughts!','07-11-2013',6,0)
insert into Ecard values('http://i.123g.us/c/efeb_valen_thanku/th/117785_th.gif','http://i.123g.us/c/efeb_valen_thanku/card/117785.swf','Valentine Day Thank!','07-11-2013',6,0)


--thiep chuc mung ngay nghi
insert into Ecard values('http://i.123g.us/c/edec_happyholidays/th/122119_th.gif','http://i.123g.us/c/edec_happyholidays/card/122119.swf','Holiday Wishes!','07-11-2013',7,0)
insert into Ecard values('http://i.123g.us/c/edec_happyholidays/th/306214_th.jpg','http://i.123g.us/c/edec_happyholidays/card/306214.swf','>Holiday Cheer','07-11-2013',7,0)
insert into Ecard values('http://i.123g.us/c/edec_happyholidays/th/122115_th.gif','http://i.123g.us/c/edec_happyholidays/card/122115.swf','Magical Holiday','07-11-2013',7,0)
insert into Ecard values('http://i.123g.us/c/edec_happyholidays/th/122111_th.gif','http://i.123g.us/c/edec_happyholidays/card/122111.swf','Holiday Hugs!','07-11-2013',7,0)
insert into Ecard values('http://i.123g.us/c/edec_happyholidays/th/122113_th.gif','http://i.123g.us/c/edec_happyholidays/card/122113.swf','Happy Holiday Dance!','07-11-2013',7,0)
insert into Ecard values('http://i.123g.us/c/edec_happyholidays/th/122110_th.gif','http://i.123g.us/c/efeb_valen_happy/card/122434.swf','Celebrate Your Holidays!','07-11-2013',7,0)
insert into Ecard values('http://i.123g.us/c/edec_happyholidays/th/122109_th.gif','http://i.123g.us/c/edec_happyholidays/card/122109.swf','Happy Holidays!','07-11-2013',7,0)
insert into Ecard values('http://i.123g.us/c/edec_happyholidays/th/126174_th.gif','http://i.123g.us/c/edec_happyholidays/card/126174.swf','Happy Holiday Season!','07-11-2013',7,0)
insert into Ecard values('http://i.123g.us/c/edec_happyholidays/th/122112_th.gif','http://i.123g.us/c/edec_happyholidays/card/122112.swf','Chilled Out Holidays!','07-11-2013',7,0)
insert into Ecard values('http://i.123g.us/c/edec_happyholidays/th/306005_th.gif','http://i.123g.us/c/edec_happyholidays/card/306005.swf','Thinking Of You.','07-11-2013',7,0)
insert into Ecard values('http://i.123g.us/c/edec_happyholidays/th/122121_th.gif','http://i.123g.us/c/edec_happyholidays/card/122121.swf','Wish Happy Holidays!','07-11-2013',7,0)
insert into Ecard values('http://i.123g.us/c/edec_happyholidays/th/310592_th.jpg','http://i.123g.us/c/edec_happyholidays/card/310592.swf','Happy Holidays','07-11-2013',7,0)

--Thiep moi
insert into Ecard values('http://i.123g.us/c/birth_happybirthday/th/119311_th.gif','http://i.123g.us/c/birth_happybirthday/card/119311.swf','Wish You Health.','07-11-2013',1,0)
insert into Ecard values('http://i.123g.us/c/birth_happybirthday/th/114550_th.gif','http://i.123g.us/c/birth_happybirthday/card/114550.swf','A Card Full Of Surprise.','07-11-2013',1,0)
insert into Ecard values('http://i.123g.us/c/wed_wed_congrats/th/303886_th.jpg','http://i.123g.us/c/wed_wed_congrats/card/303886.swf','God Bless Your Marriage.','07-11-2013',2,0)
insert into Ecard values('http://i.123g.us/c/wed_wed_congrats/th/100008_th.gif','http://i.123g.us/c/wed_wed_congrats/card/100008.swf','Wedding Bells Ringing.','07-11-2013',2,0)
insert into Ecard values('http://i.123g.us/c/efeb_valen_happy/th/118053_th.gif','http://i.123g.us/c/efeb_valen_happy/card/118053.swf','Valentine Day','07-11-2013',6,0)
insert into Ecard values('http://i.123g.us/c/efeb_valen_happy/th/113826_th.gif','http://i.123g.us/c/efeb_valen_happy/card/113826.swf','Valentine Wishes!','07-11-2013',6,0)
insert into Ecard values('http://i.123g.us/c/edec_happyholidays/th/122119_th.gif','http://i.123g.us/c/edec_happyholidays/card/122119.swf','Holiday Wishes!','07-11-2013',7,0)
insert into Ecard values('http://i.123g.us/c/edec_happyholidays/th/306214_th.jpg','http://i.123g.us/c/edec_happyholidays/card/306214.swf','>Holiday Cheer','07-11-2013',7,0)

--Thiep gui nhieu
insert into Ecard values('http://i.123g.us/c/birth_happybirthday/th/124599_th.gif','http://i.123g.us/c/birth_happybirthday/card/124599.swf','Beautiful Flowers.','07-11-2013',1,0)
insert into Ecard values('http://i.123g.us/c/birth_happybirthday/th/117069_th.gif','http://i.123g.us/c/birth_happybirthday/card/117069.swf','An Interactive Birthday.','07-11-2013',1,0)
insert into Ecard values('http://i.123g.us/c/wed_wed_congrats/th/122236_th.gif','http://i.123g.us/c/wed_wed_congrats/card/122236.swf','Wedding Wishes For A Couple.','07-11-2013',2,0)
insert into Ecard values('http://i.123g.us/c/wed_wishes/th/115411_th.gif','http://i.123g.us/c/wed_wishes/card/115411.swf','Wedding Wishes For Lovely.','07-11-2013',2,0)
insert into Ecard values('http://i.123g.us/c/ejan_ny_happy/th/113379_th.gif','http://i.123g.us/c/ejan_ny_happy/card/113379.swf','Big New Year Hug!','07-11-2013',3,0)
insert into Ecard values('http://i.123g.us/c/ejan_ny_happy/th/113846_th.gif','http://i.123g.us/c/ejan_ny_happy/card/113846.swf','Heartfelt New Year Wishes!','07-11-2013',3,0)
insert into Ecard values('http://i.123g.us/c/edec_happyholidays/th/122115_th.gif','http://i.123g.us/c/edec_happyholidays/card/122115.swf','Magical Holiday','07-11-2013',7,0)
insert into Ecard values('http://i.123g.us/c/edec_happyholidays/th/122111_th.gif','http://i.123g.us/c/edec_happyholidays/card/122111.swf','Holiday Hugs!','07-11-2013',7,0)



go
create table sentEcard
(
	sentEcardId int primary key identity,
	customerId int foreign key references customer(customerId),
	ecardId int foreign key references Ecard(ecardId),
	dateSent datetime not null,
	title varchar(100),
	messages varchar(1000),
	--photo varchar(200),
	--video varchar(200),
	signatures varchar(25),
)
go
--select * from sentecard
--delete from sentEcard where sentecardId =3
--insert into sentEcard values(2,1,'11-21-2013','happy birthday','happy happy','hero')
--insert into sentEcard values(,2,'11-11-2013','happy birthday','happy happy','hero')
--insert into listEmail values(5,'nguoiban1234567@gmail.com','hien')
--insert into listEmail values(5,'nguoimua12@gmail.com','hien')

-- sap xep nguoc lay id
--select top 1 sentEcardId from sentEcard where customerId = 1 order by sentEcardId desc 

go
go 
create table listEmail
(
	sentEcardId int foreign key references sentEcard(sentEcardId),
	emailReceived varchar(100),
	nameReceived varchar(25)
)

--select * from listEmail

--insert into listEmail values(9,'nguoimua12@gmail.com','linh')
--insert into listEmail values(9,'nguoiban1234567@gmail.com','hien')
go
create table admins
(
	id int primary key identity,
	username varchar(25) not null,
	passwords varchar(25) not null
)
go
create table logSentMail
(
	LogSentMailId int primary key identity,
	customerId int foreign key references customer(customerId),
	nameSender varchar(25),
	emailSender varchar(100),
	ecardId int foreign key references Ecard(ecardId),
	dateSent datetime,
	title varchar(30),
	messages varchar(300),
	--photo varchar(200),
	--video varchar(200),
	signatures varchar(25),
	emailReceived varchar(100),
	nameReceived varchar(25)
)
--select * from logSentMail
--insert into logSentMail values(1,'thanh','vanthanh26041989@gmail.com',1,GETDATE(),'happy birthday','happy happy ','satthu','nguoimua12@gmail.com','linh')

-- tao cac view
create view Statistics_count
as
select typeCardName , SUM(Ecard.counts) as sent from Ecard inner join typeEcard on typeEcard.typeEcardId = Ecard.typeEcardId Group by  typeEcard.typeCardName

--select sum(sent) as total from Statistics_count

-- tinh so lan gui card trong ngay hom nay
select count(*) from sentEcard where DATEDIFF(dd,dateSent,GETDATE()) = 0